﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GraduationProject.RDBMS
{
    public class User : OpearteBase
    {
        /// <summary>
        /// 增加用户
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="Users"></param>
        /// <returns></returns>
        public bool TryInsertUser(out bool ExcuteResult, params Model.User[] Users)
        {
            ExcuteResult = false;
            try
            {
                if (Users != null && Users.Count() > 0)
                {

                    //打开数据库事务
                    TryOpenTrans();
                    //更新数据库
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("INSERT INTO [dbo].[User]");
                    Str_SQL.AppendLine("([UserAccount]");
                    Str_SQL.AppendLine(",[UserPassWd]");
                    Str_SQL.AppendLine(",[UserName]");
                    Str_SQL.AppendLine(",[HeadPortrait]");
                    Str_SQL.AppendLine(",[UserEmail]");
                    Str_SQL.AppendLine(",[EmailState]");
                    Str_SQL.AppendLine(",[UserPhone]");
                    Str_SQL.AppendLine(",[PhoneState]");
                    Str_SQL.AppendLine(",[IsAdmin]");
                    Str_SQL.AppendLine(",[IsDelete])");
                    Str_SQL.AppendLine("VALUES");
                    Str_SQL.AppendLine("(@UserAccount");
                    Str_SQL.AppendLine(",@UserPassWd");
                    Str_SQL.AppendLine(",@UserName");
                    Str_SQL.AppendLine(",@HeadPortrait");
                    Str_SQL.AppendLine(",@UserEmail");
                    Str_SQL.AppendLine(",@EmailState");
                    Str_SQL.AppendLine(",@UserPhone");
                    Str_SQL.AppendLine(",@PhoneState");
                    Str_SQL.AppendLine(",@IsAdmin");
                    Str_SQL.AppendLine(",@IsDelete)");
                    #endregion

                    if (m_Conn.Execute(Str_SQL.ToString(), Users, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }

            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="UserIDs"></param>
        /// <returns></returns>
        public bool TryDeleteUser(out bool ExcuteResult, params int[] UserIDs)
        {
            ExcuteResult = false;
            try
            {
                if (UserIDs != null && UserIDs.Length > 0)
                {
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("UPDATE [dbo].[User]");
                    Str_SQL.AppendLine("SET [IsDelete] = 1");
                    Str_SQL.AppendLine("WHERE [dbo].[User].[UserID] IN @IDs");
                    #endregion

                    TryOpenTrans();
                    if (m_Conn.Execute(Str_SQL.ToString(), new { IDs = UserIDs }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="Users"></param>
        /// <returns></returns>
        public bool TryUpdateUser(out bool ExcuteResult, params Model.User[] Users)
        {
            ExcuteResult = false;
            try
            {
                if (Users != null && Users.Length > 0)
                {
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("UPDATE [dbo].[User]");
                    Str_SQL.AppendLine("SET [UserName] = @UserName");
                    Str_SQL.AppendLine(",[HeadPortrait] = @HeadPortrait");
                    Str_SQL.AppendLine(",[UserEmail] = @UserEmail");
                    Str_SQL.AppendLine(",[EmailState] = @EmailState");
                    Str_SQL.AppendLine(",[UserPhone] = @UserPhone");
                    Str_SQL.AppendLine(",[PhoneState] = @PhoneState");
                    Str_SQL.AppendLine("WHERE [UserID] = @UserID");
                    #endregion

                    TryOpenTrans();
                    if (m_Conn.Execute(Str_SQL.ToString(), Users, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 修改用户是否为管理员
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="Users"></param>
        /// <returns></returns>
        public bool TryUpdateUserIsAdmin(out bool ExcuteResult, int UserID,bool IsAdmin = false)
        {
            ExcuteResult = false;
            try
            {
                if (UserID > 0)
                {
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("UPDATE [dbo].[User]");
                    Str_SQL.AppendLine("SET [IsAdmin] = @IsAdmin");
                    Str_SQL.AppendLine("WHERE [UserID] = @UserID");
                    #endregion

                    TryOpenTrans();
                    if (m_Conn.Execute(Str_SQL.ToString(), new { UserID, IsAdmin }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="Users"></param>
        /// <returns></returns>
        public bool TryUpdateUserPassWd(out bool ExcuteResult, int UserID,string NewUserPassWd, string OldUserPassWd)
        {
            ExcuteResult = false;
            try
            {
                if (UserID > 0 && !string.IsNullOrEmpty(NewUserPassWd) && !string.IsNullOrEmpty(OldUserPassWd))
                {
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("UPDATE [dbo].[User]");
                    Str_SQL.AppendLine("SET [UserPassWd] = @NewUserPassWd");
                    Str_SQL.AppendLine("WHERE ");
                    Str_SQL.AppendLine("[dbo].[User].[UserID] = @UserID ");
                    Str_SQL.AppendLine("AND [dbo].[User].[UserPassWd] = @OldUserPassWd");
                    #endregion

                    TryOpenTrans();
                    if (m_Conn.Execute(Str_SQL.ToString(), new { UserID, NewUserPassWd,  OldUserPassWd }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="Users"></param>
        /// <returns></returns>
        public bool TryUpdateUserPassWdByAdmin(out bool ExcuteResult, int UserID, string NewUserPassWd)
        {
            ExcuteResult = false;
            try
            {
                if (UserID > 0 && !string.IsNullOrEmpty(NewUserPassWd))
                {
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("UPDATE [dbo].[User]");
                    Str_SQL.AppendLine("SET [UserPassWd] = @NewUserPassWd");
                    Str_SQL.AppendLine("WHERE ");
                    Str_SQL.AppendLine("[dbo].[User].[UserID] = @UserID ");
                    #endregion

                    TryOpenTrans();
                    if (m_Conn.Execute(Str_SQL.ToString(), new { UserID, NewUserPassWd }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 查询用户信息
        /// </summary>
        /// <param name="Users"></param>
        /// <param name="IsHaveDeleteItem"></param>
        /// <param name="UserIDs"></param>
        /// <returns></returns>
        public bool TrySelectUser(out Model.User[] Users, bool IsHaveDeleteItem = false,params int[] UserIDs)
        {
            //预先定义返回值
            Users = null;
            try
            {
                StringBuilder Str_SQL_Where = new StringBuilder();
                #region 封装Where条件语句
                if (UserIDs != null && UserIDs.Length > 0)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[UserID] IN @IDs");
                    }
                }
                if (!IsHaveDeleteItem)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[IsDelete] = 0");
                    }
                }
                #endregion

                StringBuilder Str_Sql = new StringBuilder();
                #region 封装SQL语句
                Str_Sql.AppendLine("SELECT * FROM [dbo].[User]");

                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_Sql.Append(Str_SQL_Where);
                }
                #endregion

                var SQL_Data = m_Conn.Query<Model.User>(Str_Sql.ToString(), new { IDs = UserIDs }, m_Transaction);
                if (SQL_Data != null && SQL_Data.Count() > 0)
                {
                    Users = SQL_Data.ToArray();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return false;
        }
        /// <summary>
        /// 分页查询用户
        /// </summary>
        /// <returns></returns>
        public bool TrySelectUserByPage(out Model.User[] Novels, out int TotalItemCount, int PageNumber = 1, int PageSize = 20)
        {
            //预先赋初始值
            Novels = null;
            TotalItemCount = 0;
            try
            {
                StringBuilder Str_SQL_Where = new StringBuilder();
                #region 封装查询条件SQL语句
                #endregion

                StringBuilder Str_SQL_PageSelect = new StringBuilder();
                #region 封装查询语句
                Str_SQL_PageSelect.AppendLine("SELECT DISTINCT * FROM [dbo].[User]");

                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_SQL_PageSelect.Append(Str_SQL_Where);
                }

                Str_SQL_PageSelect.AppendLine("Order By [dbo].[User].[UserID] ASC OFFSET (@PageNumber - 1) * @PageSize ROW FETCH NEXT @PageSize ROW ONLY ");
                #endregion


                StringBuilder Str_SQL_SelectCount = new StringBuilder();
                #region 封装数量查询语句
                Str_SQL_SelectCount.AppendLine("SELECT");
                Str_SQL_SelectCount.AppendLine("Count([dbo].[User].[UserID])");
                Str_SQL_SelectCount.AppendLine("FROM");
                Str_SQL_SelectCount.AppendLine("[dbo].[User]");
                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_SQL_SelectCount.Append(Str_SQL_Where);
                }
                #endregion

                //获取总条数
                TotalItemCount = m_Conn.ExecuteScalar<int>(Str_SQL_SelectCount.ToString(), new
                {
                    PageNumber,
                    PageSize
                });

                //判断是否存在数据
                if (TotalItemCount == 0)
                {
                    return false;
                }

                //获取分页数据
                var SQL_Data = m_Conn.Query<Model.User>(Str_SQL_PageSelect.ToString(), new
                {
                    PageNumber,
                    PageSize
                });
                //判断是否存在数据
                if (SQL_Data != null && SQL_Data.Count() > 0)
                {
                    Novels = SQL_Data.ToArray();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return false;
        }
        /// <summary>
        /// 通过账户名获取用户信息
        /// </summary>
        /// <returns></returns>
        public bool TrySelectUserByAccount(out Model.User[] Users, bool IsHaveDeleteItem = false, params string[] UserAccount)
        {
            //预先定义返回值
            Users = null;
            try
            {
                StringBuilder Str_SQL_Where = new StringBuilder();
                #region 封装Where条件语句
                if (UserAccount != null && UserAccount.Length > 0)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[UserAccount] IN @UserAccount");
                    }
                }
                if (!IsHaveDeleteItem)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[IsDelete] = 0");
                    }
                }
                #endregion

                StringBuilder Str_Sql = new StringBuilder();
                #region 封装SQL语句
                Str_Sql.AppendLine("SELECT DISTINCT * FROM [dbo].[User]");

                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_Sql.Append(Str_SQL_Where);
                }
                #endregion

                var SQL_Data = m_Conn.Query<Model.User>(Str_Sql.ToString(), new { UserAccount = UserAccount }, m_Transaction);
                if (SQL_Data != null && SQL_Data.Count() > 0)
                {
                    Users = SQL_Data.ToArray();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return false;
        }
        /// <summary>
        /// 通过UserEmail获取用户信息
        /// </summary>
        /// <returns></returns>
        public bool TrySelectUserByEmail(out Model.User[] Users, bool IsHaveDeleteItem = false, params string[] UserEmail)
        {
            //预先定义返回值
            Users = null;
            try
            {
                StringBuilder Str_SQL_Where = new StringBuilder();
                #region 封装Where条件语句
                if (UserEmail != null && UserEmail.Length > 0)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[UserEmail] IN @UserEmail");
                    }
                }
                if (!IsHaveDeleteItem)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[IsDelete] = 0");
                    }
                }
                #endregion

                StringBuilder Str_Sql = new StringBuilder();
                #region 封装SQL语句
                Str_Sql.AppendLine("SELECT DISTINCT * FROM [dbo].[User]");

                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_Sql.Append(Str_SQL_Where);
                }
                #endregion

                var SQL_Data = m_Conn.Query<Model.User>(Str_Sql.ToString(), new { UserEmail = UserEmail }, m_Transaction);
                if (SQL_Data != null && SQL_Data.Count() > 0)
                {
                    Users = SQL_Data.ToArray();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return false;
        }
        /// <summary>
        /// 通过UserPhone获取用户信息
        /// </summary>
        /// <returns></returns>
        public bool TrySelectUserByPhone(out Model.User[] Users, bool IsHaveDeleteItem = false, params string[] UserPhone)
        {
            //预先定义返回值
            Users = null;
            try
            {
                StringBuilder Str_SQL_Where = new StringBuilder();
                #region 封装Where条件语句
                if (UserPhone != null && UserPhone.Length > 0)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[UserPhone] IN @UserPhone");
                    }
                }
                if (!IsHaveDeleteItem)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[User].[IsDelete] = 0");
                    }
                }
                #endregion

                StringBuilder Str_Sql = new StringBuilder();
                #region 封装SQL语句
                Str_Sql.AppendLine("SELECT * FROM [dbo].[User]");

                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_Sql.Append(Str_SQL_Where);
                }
                #endregion

                var SQL_Data = m_Conn.Query<Model.User>(Str_Sql.ToString(), new { UserPhone = UserPhone }, m_Transaction);
                if (SQL_Data != null && SQL_Data.Count() > 0)
                {
                    Users = SQL_Data.ToArray();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return false;
        }
        /// <summary>
        /// 查看用户名是否存在
        /// </summary>
        /// <param name="IsExist"></param>
        /// <param name="UserAccounts"></param>
        /// <returns></returns>
        public bool TryGetIsAccountExist(out bool IsExist, params string[] UserAccount)
        {
            //预先赋值返回值
            IsExist = true;
            try
            {
                if (UserAccount != null && UserAccount.Length > 0)
                {
                    //连接数据库
                    var Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("SELECT ");
                    Str_SQL.AppendLine("COUNT([dbo].[User].[UserAccount]) ");
                    Str_SQL.AppendLine("FROM ");
                    Str_SQL.AppendLine("[dbo].[User] ");
                    Str_SQL.AppendLine("WHERE ");
                    Str_SQL.AppendLine("[dbo].[User].[UserAccount] IN @UserAccount");
                    #endregion
                    if (m_Conn.ExecuteScalar<int>(Str_SQL.ToString(), new { UserAccount }, m_Transaction) > 0)
                    {
                        IsExist = true;
                        IsDone &= true;
                    }
                    else
                    {
                        IsExist = false;
                        IsDone &= true;
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 查看手机号是否存在
        /// </summary>
        /// <param name="IsExist"></param>
        /// <param name="UserAccounts"></param>
        /// <returns></returns>
        public bool TryGetIsPhoneExist(out bool IsExist, params string[] UserPhone)
        {
            //预先赋值返回值
            IsExist = true;
            try
            {
                if (UserPhone != null && UserPhone.Length > 0)
                {
                    //连接数据库
                    var Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("SELECT ");
                    Str_SQL.AppendLine("COUNT([dbo].[User].[UserAccount]) ");
                    Str_SQL.AppendLine("FROM ");
                    Str_SQL.AppendLine("[dbo].[User] ");
                    Str_SQL.AppendLine("WHERE ");
                    Str_SQL.AppendLine("[dbo].[User].[UserPhone] IN @UserPhone");
                    #endregion
                    if (m_Conn.ExecuteScalar<int>(Str_SQL.ToString(), new { UserPhone }, m_Transaction) > 0)
                    {
                        IsExist = true;
                        IsDone &= true;
                    }
                    else
                    {
                        IsExist = false;
                        IsDone &= true;
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 查看邮件是否存在
        /// </summary>
        /// <param name="IsExist"></param>
        /// <param name="UserAccounts"></param>
        /// <returns></returns>
        public bool TryGetIsEmailExist(out bool IsExist, params string[] UserEmail)
        {
            //预先赋值返回值
            IsExist = true;
            try
            {
                if (UserEmail != null && UserEmail.Length > 0)
                {
                    //连接数据库
                    var Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("SELECT ");
                    Str_SQL.AppendLine("COUNT([dbo].[User].[UserAccount]) ");
                    Str_SQL.AppendLine("FROM ");
                    Str_SQL.AppendLine("[dbo].[User] ");
                    Str_SQL.AppendLine("WHERE ");
                    Str_SQL.AppendLine("[dbo].[User].[UserEmail] IN @UserEmail");
                    #endregion
                    if (m_Conn.ExecuteScalar<int>(Str_SQL.ToString(), new { UserEmail }, m_Transaction) > 0)
                    {
                        IsExist = true;
                        IsDone &= true;
                    }
                    else
                    {
                        IsExist = false;
                        IsDone &= true;
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return IsDone;
        }
    }
}
